CREATE NONCLUSTERED INDEX IX_Meter_LocationID
ON Meter(LocationID ASC)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_DataOperationID
ON DataOperationFailure(DataOperationID ASC)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_FileGroupID
ON DataOperationFailure(FileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CustomerMeter_CustomerID
ON CustomerMeter(CustomerID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CustomerMeter_MeterID
ON CustomerMeter(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AssetChannel_AssetID
ON AssetChannel(AssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AssetChannel_ChannelID
ON AssetChannel(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Series_ChannelID
ON Series(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Series_SeriesTypeID
ON Series(SeriesTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_MaintenanceWindow_MeterID
ON MaintenanceWindow(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerChannel_ChannelID
ON BreakerChannel(ChannelID ASC)
GO

CREATE TABLE SCADAPoint
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    BreakerID INT NOT NULL FOREIGN KEY REFERENCES Asset(ID),
    Point VARCHAR(200) NOT NULL
)
GO

ALTER TABLE ScheduledEmailType ADD
	RequireApproval BIT NOT NULL DEFAULT 0,
	ShowSubscription BIT NOT NULL DEFAULT 1
GO

CREATE NONCLUSTERED INDEX IX_SentEmail_EmailTypeID
ON SentEmail(EmailTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_SentEmail_TimeSent
ON SentEmail(TimeSent ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_PhaseID
ON BreakerRestrike(PhaseID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerOperation_PhaseID
ON BreakerOperation(PhaseID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerOperation_BreakerOperationTypeID
ON BreakerOperation(BreakerOperationTypeID ASC)
GO

DROP INDEX IX_EventSentEmail_EventID
GO

CREATE NONCLUSTERED INDEX IX_EventSentEmail_EventID
ON EventSentEmail(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_EventSentEmail_SentEmailID
ON EventSentEmail(SentEmailID ASC)
GO

CREATE NONCLUSTERED INDEX IX_SnapshotHarmonics_ChannelID
ON SnapshotHarmonics(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_OutputChannel_SeriesID
ON OutputChannel(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSegment_EventID
ON FaultSegment(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSegment_SegmentTypeID
ON FaultSegment(SegmentTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSummary_LineSegmentID
ON FaultSummary(LineSegmentID ASC)
GO

CREATE NONCLUSTERED INDEX IX_NearestStructure_FaultSummaryID
ON NearestStructure(FaultSummaryID ASC)
GO

CREATE NONCLUSTERED INDEX IX_NearestStructure_StructureID
ON NearestStructure(StructureID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LightningStrike_EventID
ON LightningStrike(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_VaisalaExtendedLightningData_LightningStrikeID
ON VaisalaExtendedLightningData(LightningStrikeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_AlarmID
ON AlarmLog(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_AlarmFactorID
ON AlarmLog(AlarmFactorID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_SeverityID
ON AlarmLog(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_RemoteXDAInstanceID
ON FileGroupLocalToRemote(RemoteXDAInstanceID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_LocalFileGroupID
ON FileGroupLocalToRemote(LocalFileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_RemoteFileGroupID
ON FileGroupLocalToRemote(RemoteFileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_CompanyID
ON CompanyMeter(CompanyID) 
GO

CREATE NONCLUSTERED INDEX IX_Report_MeterID
ON Report(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_EventStat_EventID
ON EventStat(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_RelayPerformance_EventID
ON RelayPerformance(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_RelayPerformance_ChannelID
ON RelayPerformance(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBAnalyticResult_EventID
ON CBAnalyticResult(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBAnalyticResult_PhaseID
ON CBAnalyticResult(PhaseID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBRestrikeResult_CBResultID
ON CBRestrikeResult(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBRestrikeResult_CBRestrikeTypeID
ON CBRestrikeResult(CBRestrikeTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBSwitchHealthAnalytic_CBResultID
ON CBSwitchHealthAnalytic(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBSwitchHealthAnalytic_CBSwitchingConditionID
ON CBSwitchHealthAnalytic(CBSwitchingConditionID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBResultID
ON CBCapBankResult(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBBankHealthID
ON CBCapBankResult(CBBankHealthID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBOperationID
ON CBCapBankResult(CBOperationID ASC)
GO

DROP VIEW ActiveSubscription
GO

CREATE VIEW ActiveSubscription AS
    SELECT
        UserAccountEmailID,
        UserAccountID,
        Approved,
        AssetGroup,
        EmailName,
        Category,
        EmailTypeID,
        Subject,
        LastSent,
        UserName,
        FirstName,
        LastName,
        Email,
        RequireApproval
    FROM (
        SELECT 
            UserAccountEmailType.ID AS UserAccountEmailID,
            UserAccountEmailType.UserAccountID,
            UserAccountEmailType.Approved,
            AssetGroup.Name AS AssetGroup,
            EmailType.Name AS EmailName,
            EmailCategory.Name AS Category,
            EmailType.ID AS EmailTypeID,
            SentEmail.Subject AS Subject,
            SentEmail.TimeSent AS LastSent,
            UserAccount.Name AS UserName,
            UserAccount.FirstName AS FirstName,
            UserAccount.LastName AS LastName,
            UserAccount.Email,
            EmailType.RequireApproval,
            ROW_NUMBER() OVER (
                PARTITION BY UserAccountEmailType.ID
                ORDER BY SentEmail.TimeSent DESC
            ) AS rn
        FROM UserAccountEmailType LEFT JOIN
            AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID LEFT JOIN
            EmailType ON UserAccountEmailType.EmailTypeID = EmailType.ID LEFT JOIN
            EmailCategory ON EmailCategory.ID = EmailType.EmailCategoryID LEFT JOIN
            SentEmail ON SentEmail.EmailTypeID = EmailType.ID LEFT JOIN
            UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID
    ) AS recent WHERE rn = 1
GO

ALTER TABLE [SEBrowser.Setting]
	ADD [DefaultValue] [varchar](512) NULL
GO

ALTER TABLE [SEBrowser.Setting]
	ADD CONSTRAINT DF_SEBrowser_Setting_Scope DEFAULT 'app.setting' FOR Scope
GO

ALTER TABLE [SEBrowser.Setting]
	ADD CONSTRAINT DF_SEBrowser_Setting_Roles DEFAULT 'Administrator' FOR Roles
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.EmailDuplicateThresholdMinutes', '0', '0')
GO

EXEC UniversalCascadeDelete 'DataOperation','TypeName = ''FaultData.DataOperations.DailyStatisticOperation'' OR TypeName = ''FaultData.DataOperations.LSCVSDataOperation'''
GO